﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace permit.App_Code.DataAccess
{
    public class AssginAccess
    {

        string strConn = ConfigurationManager.ConnectionStrings["permitConnectionString"].ConnectionString;
        SqlConnection conn;

        public SqlConnection GetConnetcion(string connString)
        {
            conn = new SqlConnection(connString);
            if (conn.State == ConnectionState.Closed)
            {

                conn.Open();
            }
            return conn;
        }

        public DataSet GetAssgins()
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT AssginTable.as_ID, AssginTable.au_ID, AssginTable.user_ID,UserTable.user_Name FROM AssginTable INNER JOIN UserTable ON AssginTable.user_ID = UserTable.user_ID ORDER BY UserTable.Name";

            command.Connection = GetConnetcion(strConn);

            SqlDataAdapter da = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
                da.Dispose();
                connClose();
                return ds;
            }
            catch (Exception ex)
            {
                da.Dispose();
                connClose();
                return null;
            }
        }

        public void DeleteAssgin(int userID, int auID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "DELETE FROM AssginTable WHERE (user_ID = @user_ID and au_ID = @au_ID)";
            command.Connection = GetConnetcion(strConn);

            command.Parameters.Add("@user_ID", SqlDbType.Int, 4, "user_ID").Value = userID;
            command.Parameters.Add("@au_ID", SqlDbType.Int, 4, "au_ID").Value = auID;

            command.ExecuteNonQuery();
            connClose();
        }

        public void InsertAssgin(int userID, int auID)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "INSERT INTO AssginTable(user_ID, au_ID)VALUES (@user_ID,@au_ID)";

            command.Connection = GetConnetcion(strConn);

            command.Parameters.Add("@user_ID", SqlDbType.Int, 4, "user_ID").Value = userID;
            command.Parameters.Add("@au_ID", SqlDbType.Int, 4, "au_ID").Value = auID;

            command.ExecuteNonQuery();
            connClose();

        }

        public DataSet GetAu_IDs(int userID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT au_ID FROM AssginTable where user_ID = @user_ID";

            command.Connection = GetConnetcion(strConn);
            command.Parameters.Add("@user_ID", SqlDbType.Int, 4, "user_ID").Value = userID;

            SqlDataAdapter da = new SqlDataAdapter(command);

            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
                da.Dispose();
                connClose();
                return ds;
            }
            catch (Exception ex)
            {
                connClose();
                return null;
            }
        }

        //是否拥有打印许可证权限
        public bool IsPrintAssgin(int userID)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "SELECT au_ID FROM AssginTable where user_ID = @user_ID and au_ID = 6 ";
            command.Connection = GetConnetcion(strConn);
            command.Parameters.Add("@user_ID", SqlDbType.Int, 4, "user_ID").Value = userID;

            int count = 0;
            try
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {

                    count = Convert.ToInt32(dr["au_ID"].ToString());
                }
                if (count > 0)
                {
                    connClose();
                    return true;
                }
                else
                {
                    connClose();
                    return false;
                }
            }
            catch (Exception ex)
            {
                connClose();
                return false;
            }

        }

        //是否拥有录入许可证权限
        public bool IsInputAssgin(int userID)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "SELECT au_ID FROM AssginTable where user_ID = @user_ID and au_ID = 1 ";
            command.Connection = GetConnetcion(strConn);
            command.Parameters.Add("@user_ID", SqlDbType.Int, 4, "user_ID").Value = userID;

            int count = 0;
            try
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {

                    count = Convert.ToInt32(dr["au_ID"].ToString());
                }
                if (count > 0)
                {
                    connClose();
                    return true;
                }
                else
                {
                    connClose();
                    return false;
                }
            }
            catch (Exception ex)
            {
                connClose();
                return false;
            }


        }

        //是否有修改许可证权限
        public bool IsEditAssgin(int userID)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "SELECT au_ID FROM AssginTable where user_ID = @user_ID and au_ID = 5 ";
            command.Connection = GetConnetcion(strConn);
            command.Parameters.Add("@user_ID", SqlDbType.Int, 4, "user_ID").Value = userID;

            int count = 0;
            try
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {

                    count = Convert.ToInt32(dr["au_ID"].ToString());
                }
                dr.Dispose();
                connClose();
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                connClose();
                return false;
            }

        }

        //是否有人员管理权限
        public bool IsUserAssgin(int userID)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "SELECT au_ID FROM AssginTable where user_ID = @user_ID and au_ID = 2 ";
            command.Connection = GetConnetcion(strConn);
            command.Parameters.Add("@user_ID", SqlDbType.Int, 4, "user_ID").Value = userID;

            int count = 0;
            try
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {

                    count = Convert.ToInt32(dr["au_ID"].ToString());
                }
                dr.Dispose();
                connClose();
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                connClose();
                return false;
            }

        }

        public bool IsDepartmentAssgin(int userID)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "SELECT au_ID FROM AssginTable where user_ID = @user_ID and au_ID = 3 ";
            command.Connection = GetConnetcion(strConn);
            command.Parameters.Add("@user_ID", SqlDbType.Int, 4, "user_ID").Value = userID;

            int count = 0;
            try
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {

                    count = Convert.ToInt32(dr["au_ID"].ToString());
                }
                dr.Dispose();
                connClose();
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                connClose();
                return false;
            }

        }
        public bool IsAssgin(int userID)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "SELECT au_ID FROM AssginTable where user_ID = @user_ID and au_ID = 4";
            command.Connection = GetConnetcion(strConn);
            command.Parameters.Add("@user_ID", SqlDbType.Int, 4, "user_ID").Value = userID;

            int count = 0;
            try
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {

                    count = Convert.ToInt32(dr["au_ID"].ToString());
                }
                dr.Dispose();
                connClose();
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                connClose();
                return false;
            }

        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// 
        public void connClose()
        {
            if (conn.State == ConnectionState.Open)
            {//判断数据库的连接状态，如果状态是打开的话就将它关闭

                conn.Close();
            }
        }
    }
}
